﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace luanvanFinal
{
    class clsReq
    {
        String idReq;
        int idPro;
        String reqTitle;
        String reqDescription;
        String reqResource;
        String type;
        String category;
        String size;
        String difficulty;
        String reqKey;
        String priority;
        String pic;
        String coding;
        String status;
        Nullable<DateTime> change = null;
        String note;
        Nullable<DateTime> designReviewed = null;
        Nullable<DateTime> codedDate = null;
        Nullable<DateTime> testedDate = null;
        Nullable<DateTime> deliveredDate = null;
        Nullable<DateTime> acceptedDate = null;
        Nullable<DateTime> cancelledDate = null;

        public String IdReq
        {
            get { return idReq; }
            set { idReq = value; }
        }

        public int IdPro
        {
            get { return idPro; }
            set { idPro = value; }
        }

        public String ReqTitle
        {
            get { return reqTitle; }
            set { reqTitle = value; }
        }

        public String ReqDescription
        {
            get { return reqDescription; }
            set { reqDescription = value; }
        }

        public String ReqResource
        {
            get { return reqResource; }
            set { reqResource = value; }
        }

        public String Type
        {
            get { return type; }
            set { type = value; }
        }

        public String Category
        {
            get { return category; }
            set { category = value; }
        }

        public String Size
        {
            get { return size; }
            set { size = value; }
        }

        public String Difficulty
        {
            get { return difficulty; }
            set { difficulty = value; }
        }

        public String ReqKey
        {
            get { return reqKey; }
            set { reqKey = value; }
        }

        public String Priority
        {
            get { return priority; }
            set { priority = value; }
        }

        public String Pic
        {
            get { return pic; }
            set { pic = value; }
        }

        public String Coding
        {
            get { return coding; }
            set { coding = value; }
        }

        public String Status
        {
            get { return status; }
            set { status = value; }
        }

        public Nullable<DateTime> Change
        {
            get { return change; }
            set { change = value; }
        }

        public String Note
        {
            get { return note; }
            set { note = value; }
        }

        public Nullable<DateTime> DesignReviewed
        {
            get { return designReviewed; }
            set { designReviewed = value; }
        }

        public Nullable<DateTime> CodedDate
        {
            get { return codedDate; }
            set { codedDate = value; }
        }

        public Nullable<DateTime> TestedDate
        {
            get { return testedDate; }
            set { testedDate = value; }
        }

        public Nullable<DateTime> DeliveredDate
        {
            get { return deliveredDate; }
            set { deliveredDate = value; }
        }

        public Nullable<DateTime> AcceptedDate
        {
            get { return acceptedDate; }
            set { acceptedDate = value; }
        }

        public Nullable<DateTime> CancelledDate
        {
            get { return cancelledDate; }
            set { cancelledDate = value; }
        }

        public clsReq()
        {
            idReq = "";
            idPro = 0;
            reqTitle = "";
            reqDescription = "";
            reqResource = "";
            type = "";
            category = "";
            size = "";
            difficulty = "";
            reqKey = "";
            priority = "";
            pic = "";
            coding = "";
            status = "";
            change = null;
            note = "";
            designReviewed = null;
            codedDate = null;
            testedDate = null;
            deliveredDate = null;
            acceptedDate = null;
            cancelledDate = null;
        }

        public void insertReq()
        {
            clsConnect ketnoi = new clsConnect();
            ketnoi.openConnection();
            try
            {
                SqlCommand sqlcmd = new SqlCommand("insertReq", ketnoi.Connection);
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.Parameters.Add("@idReq", SqlDbType.NVarChar).Value = this.idReq;
                sqlcmd.Parameters.Add("@idPro", SqlDbType.Int).Value = this.idPro;
                sqlcmd.Parameters.Add("@reqTitle", SqlDbType.NVarChar).Value = this.reqTitle;
                sqlcmd.Parameters.Add("@reqDescription", SqlDbType.NVarChar).Value = this.reqDescription;
                sqlcmd.Parameters.Add("@reqResource", SqlDbType.NVarChar).Value = this.reqResource;
                sqlcmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = this.type;
                sqlcmd.Parameters.Add("@category", SqlDbType.NVarChar).Value = this.category;
                sqlcmd.Parameters.Add("@size", SqlDbType.NVarChar).Value = this.size;
                sqlcmd.Parameters.Add("@difficulty", SqlDbType.NVarChar).Value = this.difficulty;
                sqlcmd.Parameters.Add("@reqKey", SqlDbType.NVarChar).Value = this.reqKey;
                sqlcmd.Parameters.Add("@priority", SqlDbType.NVarChar).Value = this.priority;
                sqlcmd.Parameters.Add("@pic", SqlDbType.NVarChar).Value = this.pic;
                sqlcmd.Parameters.Add("@coding", SqlDbType.NVarChar).Value = this.coding;
                sqlcmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = this.status;
                sqlcmd.Parameters.Add("@change", SqlDbType.DateTime).Value = this.change;
                sqlcmd.Parameters.Add("@note", SqlDbType.NVarChar).Value = this.note;
                sqlcmd.Parameters.Add("@designReviewed", SqlDbType.DateTime).Value = this.designReviewed;
                sqlcmd.Parameters.Add("@codedDate", SqlDbType.DateTime).Value = this.codedDate;
                sqlcmd.Parameters.Add("@testedDate", SqlDbType.DateTime).Value = this.testedDate;
                sqlcmd.Parameters.Add("@deliveredDate", SqlDbType.DateTime).Value = this.deliveredDate;
                sqlcmd.Parameters.Add("@acceptedDate", SqlDbType.DateTime).Value = this.acceptedDate;
                sqlcmd.Parameters.Add("@cancelledDate", SqlDbType.DateTime).Value = this.cancelledDate;

                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Dữ liệu đã tồn tại hoặc không hợp lệ!!!" + ex.ToString(), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            ketnoi.closeConnection();
        }

        public clsReq getReqFromID(string reqID, string proID )
        {
            
            clsReq req = new clsReq();
            clsConnect ketnoi = new clsConnect();
            ketnoi.openConnection();
            string strSQL = "select * from req where idReq = '" + reqID + "' and idPro='" + proID + "'";
            SqlDataAdapter da = new SqlDataAdapter(strSQL, ketnoi.Connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            ketnoi.closeConnection();
            int idPro; 
            int.TryParse(dt.Rows[0][1].ToString(), out idPro);
            req.IdReq = dt.Rows[0][0].ToString();
            req.IdPro = idPro;
            req.ReqTitle = dt.Rows[0][2].ToString();
            req.ReqDescription = dt.Rows[0][3].ToString();
            req.ReqResource = dt.Rows[0][4].ToString();
            req.Type = dt.Rows[0][5].ToString();
            req.Category = dt.Rows[0][6].ToString();
            req.Size = dt.Rows[0][7].ToString();
            req.difficulty = dt.Rows[0][8].ToString();
            req.ReqKey = dt.Rows[0][9].ToString();
            req.Priority = dt.Rows[0][10].ToString();
            req.Pic = dt.Rows[0][11].ToString();
            req.Coding = dt.Rows[0][12].ToString();
            req.Status = dt.Rows[0][13].ToString();
            if (dt.Rows[0][14].ToString() == "")
                req.Change = null;
            else
                req.Change = DateTime.Parse(dt.Rows[0][14].ToString());
            req.Note = dt.Rows[0][15].ToString();
            if (dt.Rows[0][17].ToString() == "")
                req.DesignReviewed = null;
            else
                req.DesignReviewed = DateTime.Parse(dt.Rows[0][16].ToString());
            if (dt.Rows[0][17].ToString() == "")
                req.CodedDate = null;
            else
                req.CodedDate = DateTime.Parse(dt.Rows[0][17].ToString());
            if (dt.Rows[0][18].ToString() == "")
                req.CodedDate = null;
            else
                req.TestedDate = DateTime.Parse(dt.Rows[0][18].ToString());
            if (dt.Rows[0][19].ToString() == "")
                req.DeliveredDate = null;
            else
                req.DeliveredDate = DateTime.Parse(dt.Rows[0][19].ToString());
            if (dt.Rows[0][20].ToString() == "")
                req.AcceptedDate = null;
            else
                req.AcceptedDate = DateTime.Parse(dt.Rows[0][20].ToString());
            if (dt.Rows[0][21].ToString() == "")
                req.CancelledDate = null;
            else
                req.CancelledDate = DateTime.Parse(dt.Rows[0][21].ToString());
            return req;
        }

        public void updateReq()
        {
            clsConnect ketnoi = new clsConnect();
            ketnoi.openConnection();
            //try
            //{
            SqlCommand sqlcmd = new SqlCommand("updateReq", ketnoi.Connection);
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Parameters.Add("@idReq", SqlDbType.NVarChar).Value = this.idReq;
            sqlcmd.Parameters.Add("@idPro", SqlDbType.Int).Value = this.idPro;
            sqlcmd.Parameters.Add("@reqTitle", SqlDbType.NVarChar).Value = this.reqTitle;
            sqlcmd.Parameters.Add("@reqDescription", SqlDbType.NVarChar).Value = this.reqDescription;
            sqlcmd.Parameters.Add("@reqResource", SqlDbType.NVarChar).Value = this.reqResource;
            sqlcmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = this.type;
            sqlcmd.Parameters.Add("@category", SqlDbType.NVarChar).Value = this.category;
            sqlcmd.Parameters.Add("@size", SqlDbType.NVarChar).Value = this.size;
            sqlcmd.Parameters.Add("@difficulty", SqlDbType.NVarChar).Value = this.difficulty;
            sqlcmd.Parameters.Add("@reqKey", SqlDbType.NVarChar).Value = this.reqKey;
            sqlcmd.Parameters.Add("@priority", SqlDbType.NVarChar).Value = this.priority;
            sqlcmd.Parameters.Add("@pic", SqlDbType.NVarChar).Value = this.pic;
            sqlcmd.Parameters.Add("@coding", SqlDbType.NVarChar).Value = this.coding;
            sqlcmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = this.status;
            sqlcmd.Parameters.Add("@change", SqlDbType.DateTime).Value = this.change;
            sqlcmd.Parameters.Add("@note", SqlDbType.NVarChar).Value = this.note;
            sqlcmd.Parameters.Add("designReviewed", SqlDbType.DateTime).Value = this.designReviewed;
            sqlcmd.Parameters.Add("@codedDate", SqlDbType.DateTime).Value = this.codedDate;
            sqlcmd.Parameters.Add("@testedDate", SqlDbType.DateTime).Value = this.testedDate;
            sqlcmd.Parameters.Add("@deliveredDate", SqlDbType.DateTime).Value = this.deliveredDate;
            sqlcmd.Parameters.Add("@acceptedDate", SqlDbType.NVarChar).Value = this.acceptedDate;
            sqlcmd.Parameters.Add("@cancelledDate", SqlDbType.NVarChar).Value = this.cancelledDate;
            sqlcmd.ExecuteNonQuery();
            //}
            //catch (Exception ex)
            //{
            //MessageBox.Show("Dữ liệu đã tồn tại hoặc không hợp lệ!!!" + ex.ToString(), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
            //}
            ketnoi.closeConnection();
        }

        public void deleteReq()
        {
            clsConnect ketnoi = new clsConnect();
            ketnoi.openConnection();
            try
            {
                SqlCommand sqlcmd = new SqlCommand("deleteReq", ketnoi.Connection);
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.Parameters.Add("@idReq", SqlDbType.NVarChar).Value = this.idReq;
                sqlcmd.Parameters.Add("@idPro", SqlDbType.Int).Value = this.idPro;
                sqlcmd.ExecuteNonQuery();
                MessageBox.Show("You have delete success !!!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch
            {
                MessageBox.Show("You have error while deleting project!!!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            ketnoi.closeConnection();
        }
        public void deleteReqByIdProject()
        {
            clsConnect ketnoi = new clsConnect();
            ketnoi.openConnection();
            try
            {
                SqlCommand sqlcmd = new SqlCommand("deleteReqByIdProject", ketnoi.Connection);
                sqlcmd.CommandType = CommandType.StoredProcedure;
                //sqlcmd.Parameters.Add("@idReq", SqlDbType.NVarChar).Value = this.idReq;
                sqlcmd.Parameters.Add("@idPro", SqlDbType.Int).Value = this.idPro;
                sqlcmd.ExecuteNonQuery();
                //MessageBox.Show("You have delete success !!!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch
            {
                MessageBox.Show("You have error while deleting project!!!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            ketnoi.closeConnection();
        }
        public String getIdFromName(string name)
        {
            try
            {
                String strSQL = "select ID from req where reqTitle = '" + name + "'";
                clsConnect ketnoi = new clsConnect();
                ketnoi.openConnection();
                SqlDataAdapter da = new SqlDataAdapter(strSQL, ketnoi.Connection);
                DataTable dt = new DataTable();
                da.Fill(dt);
                ketnoi.closeConnection();
                return dt.Rows[0][0].ToString();
            }
            catch
            {
                return null;
            }
        }

        public static DataTable getDataProject(String strSQL)
        {
            clsConnect ketnoi = new clsConnect();
            ketnoi.openConnection();
            SqlDataAdapter da = new SqlDataAdapter(strSQL, ketnoi.Connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            ketnoi.closeConnection();
            return dt;
        }
    }
}
